using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace Common.Utilities.Persistence
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DatabasePersistenceUtil<T> : ISerializable<T>
        where T : IDbSerializable
    {

        #region ISerializable<T> Members

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="entity"></param>
        public void Save(string path, ref T entity)
        {
            List<Tuple> fields = EntityFieldMappingHelper.GetSimpleFields(entity);

            if (fields == null || fields.Count == 0)
                throw new Exception("The object need to have properties mapped to table fields");

            SqlConnection conn = new SqlConnection(entity.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                bool insertNew = false;
                if (entity.ID <= 0)
                    insertNew = true;
                if (insertNew)
                {
                    string insertSql = "insert into [" + entity.TableName + "] (";
                    if (entity.PKIsIdentity)
                    {
                        for (int i = 0; i < fields.Count; i++)
                        {
                            if (fields[i].Name.ToLower() != entity.PkFieldName.ToLower())
                            {
                                insertSql += "[" + fields[i].Name + "]";
                            }
                            if (i < fields.Count - 1)
                            {
                                insertSql += ",";
                            }
                        }
                        if (insertSql.EndsWith(","))
                        {
                            insertSql = insertSql.Substring(0, insertSql.Length - 1);
                        }
                        insertSql += ") values (";
                        for (int i = 0; i < fields.Count; i++)
                        {
                            if (fields[i].Name.ToLower() != entity.PkFieldName.ToLower())
                            {
                                if (fields[i].Value == null)
                                    insertSql += "null";
                                else
                                {
                                    if (fields[i].IsQuoteType)
                                    {
                                        insertSql += "'" + fields[i].Value + "'";
                                    }
                                    else
                                    {
                                        insertSql += fields[i].Value;
                                    }
                                }
                            }
                            if (i < fields.Count - 1)
                            {
                                insertSql += ",";
                            }
                        }
                        if (insertSql.EndsWith(","))
                        {
                            insertSql = insertSql.Substring(0, insertSql.Length - 1);
                        }
                        insertSql += ")";
                    }
                    else
                    {
                        for (int i = 0; i < fields.Count; i++)
                        {
                            insertSql += "[" + fields[i].Name + "]";
                            if (i < fields.Count - 1)
                            {
                                insertSql += ",";
                            }
                        }
                        if (insertSql.EndsWith(","))
                        {
                            insertSql = insertSql.Substring(0, insertSql.Length - 1);
                        }
                        insertSql += ") values (";
                        for (int i = 0; i < fields.Count; i++)
                        {
                            if (fields[i].Value == null)
                                insertSql += "null";
                            else
                            {
                                if (fields[i].IsQuoteType)
                                {
                                    insertSql += "'" + fields[i].Value + "'";
                                }
                                else
                                {
                                    insertSql += fields[i].Value;
                                }
                            }
                            if (i < fields.Count - 1)
                            {
                                insertSql += ",";
                            }
                        }
                        if (insertSql.EndsWith(","))
                        {
                            insertSql = insertSql.Substring(0, insertSql.Length - 1);
                        }
                        insertSql += ")";
                    }
                    cmd.CommandText = insertSql;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select max ([" + entity.PkFieldName + "]) from [" + entity.TableName + "]";
                    entity.ID = int.Parse(cmd.ExecuteScalar().ToString());
                }
                else
                {
                    string updateSql = "update [" + entity.TableName + "] set ";
                    for (int i = 0; i < fields.Count; i++)
                    {
                        if (fields[i].Name.ToLower() != entity.PkFieldName.ToLower())
                        {
                            updateSql += "[" + fields[i].Name + "]=";
                            if (fields[i].Value == null)
                            {
                                updateSql += "null";
                            }
                            else
                            {
                                if (fields[i].IsQuoteType)
                                {
                                    updateSql += "'" + fields[i].Value + "'";
                                }
                                else
                                {
                                    updateSql += fields[i].Value;
                                }
                            }
                        }
                        if (i < fields.Count - 1)
                            updateSql += ",";
                    }
                    if (updateSql.EndsWith(","))
                        updateSql = updateSql.Substring(0, updateSql.Length - 1);
                    updateSql += " where [" + entity.PkFieldName + "]=" + entity.ID;
                    cmd.CommandText = updateSql;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetByID(string path, int id)
        {
            T entity = (T)Activator.CreateInstance<T>();
            List<NameType> fields = EntityFieldMappingHelper.GetFieldMappings(entity);
            SqlConnection conn = new SqlConnection(entity.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string selectSql = "select ";
                for (int i = 0; i < fields.Count; i++)
                {
                    selectSql += "[" + fields[i].Name + "]";
                    if (i < fields.Count - 1)
                        selectSql += ",";
                    else
                        selectSql += " from [" + entity.TableName + "]";
                }
                selectSql += " where [" + entity.PkFieldName + "]=" + id.ToString();
                cmd.CommandText = selectSql;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    for (int i = 0; i < fields.Count; i++)
                    {
                        System.Reflection.PropertyInfo prop = EntityFieldMappingHelper.GetPropertyByName(entity, fields[i].Name);
                        object fieldValue = reader[fields[i].Name];
                        prop.SetValue(entity, fieldValue, null);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return entity;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="id"></param>
        public void Delete(string path, int id)
        {
            T entity = (T)Activator.CreateInstance<T>();
            SqlConnection conn = new SqlConnection(entity.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "delete [" + entity.TableName + "] where [" +
                    entity.PkFieldName + "]=" + id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public List<T> GetAll(string path)
        {
            List<T> entities = new List<T>();
            T entityType = Activator.CreateInstance<T>();
            List<NameType> fields = EntityFieldMappingHelper.GetFieldMappings(entityType);
            SqlConnection conn = new SqlConnection(entityType.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string selectSql = "select ";
                for (int i = 0; i < fields.Count; i++)
                {
                    selectSql += "[" + fields[i].Name + "]";
                    if (i < fields.Count - 1)
                        selectSql += ",";
                    else
                        selectSql += " from [" + entityType.TableName + "]";
                }
                cmd.CommandText = selectSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    T entity = Activator.CreateInstance<T>();
                    for (int i = 0; i < fields.Count; i++)
                    {
                        System.Reflection.PropertyInfo prop = EntityFieldMappingHelper.GetPropertyByName(entity, fields[i].Name);
                        object fieldValue = reader[fields[i].Name];
                        prop.SetValue(entity, fieldValue, null);
                    }
                    entities.Add(entity);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return entities;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        public List<T> FindAll(string path, List<FieldFilter> filters)
        {
            List<T> entities = new List<T>();
            T entityType = Activator.CreateInstance<T>();
            List<NameType> fields = EntityFieldMappingHelper.GetFieldMappings(entityType);
            SqlConnection conn = new SqlConnection(entityType.ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string selectSql = "select ";
                for (int i = 0; i < fields.Count; i++)
                {
                    selectSql += "[" + fields[i].Name + "]";
                    if (i < fields.Count - 1)
                        selectSql += ",";
                    else
                        selectSql += " from [" + entityType.TableName + "]";
                }
                if (filters != null && filters.Count > 0)
                {
                    selectSql += " where ";
                    for (int i = 0; i < filters.Count; i++)
                    {
                        selectSql += "[" + filters[i].FieldName + "]";
                        selectSql += " " + filters[i].OperatorString + " ";
                        if (filters[i].Value == null)
                        {
                            selectSql += "null";
                        }
                        else
                        {
                            if (filters[i].IsQuoteType)
                            {
                                selectSql += "'" + filters[i].Value + "'";
                            }
                            else
                            {
                                selectSql += filters[i].Value;
                            }
                        }
                        if (i < filters.Count - 1)
                            selectSql += ",";
                    }
                }
                cmd.CommandText = selectSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    T entity = Activator.CreateInstance<T>();
                    for (int i = 0; i < fields.Count; i++)
                    {
                        System.Reflection.PropertyInfo prop = EntityFieldMappingHelper.GetPropertyByName(entity, fields[i].Name);
                        object fieldValue = reader[fields[i].Name];
                        prop.SetValue(entity, fieldValue, null);
                    }
                    entities.Add(entity);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return entities;
        }
        #endregion
    }
}
